/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package clientprogram;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Fourrub
 */
public class DBUtils {

    String url;
    String user;
    String password;
    static Connection connection;
    String fileData = "InternetManagements";
    Statement state;

    public void init() {
        try {
            Properties properties = new Properties();
            properties.load(new FileInputStream("database.properties"));
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            connection = DriverManager.getConnection(url + fileData, user, password);
         } catch (IllegalAccessException ex) {
            //Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            //Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            createDatabase();
            //Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            //Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            //Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, ex);
        }
        System.out.println(url);
        createStatement();
    }

    private void createDatabase() {
        try {
            String query1 = "create table COMPUTER (" + "NAMECOM int,"+"MACADDRESS varchar(30))";
            String query2 = "create table EMPLOYEE (" + "EMPLOYEEID int,"
                    + "EMPLOYEENAME varchar(30)," +"PASSWORD varchar(30),"
                    + "SALARYBONUS int)";
            String query3 = "create table MONTH (" + "MONTH varchar(30),"
                    + "YEAR varchar(30),"+ "INCOME int)";
            String query4 = "create table TRANSACTIONS (" + "BILLID int,"
                    + "NAMECOM varchar(30)," + "EMPLOYEEID int,"
                    + "USERNAME varchar(30)," + "TIMESTART varchar(30),"+ "TIMEEND varchar(30),"+ "COST int)";
            String query5 = "create table USER (" + "USERNAME varchar(30),"
                    + "PASSWORD varchar(30)," + "MINSREMAIN int,"
                    + "LASTLOGIN varchar(30)," + "LASTTRANSACTION varchar(30),"+ "LASTUSEMINS int,"+ "LASTDEPOSIT int)";
            String query6 = "create table DAY (" + "DAY int,"
                    + "MONTH int," + "YEAR int,"
                    + "INCOME int)";
            String query7 = "create table YEAR (" + "YEAR int," + "INCOME int)";
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(url, user, password);
            Statement s = connection.createStatement();
            int myResult = s.executeUpdate("CREATE DATABASE " + fileData);
            connection.close();
            connection = DriverManager.getConnection(url + fileData, user, password);
            s = connection.createStatement();
            s.execute(query1);
            s.execute(query2);
            s.execute(query3);
            s.execute(query4);
            s.execute(query5);
            s.execute(query6);
            s.execute(query7);
            
            
        } catch (ClassNotFoundException | SQLException e) {
        }
    }

    public void createStatement() {
        if (state == null) {
            try {
                state = connection.createStatement();
            } catch (SQLException ex) {
                Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public ResultSet retriveData(String command) {
        try {
            createStatement();
            ResultSet result = state.executeQuery(command);
            return result;
        } catch (SQLException ex) {
            Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public int insertdata(String sqlCommand, int[] indexes, String[] values) {
        return excuteUpdate(sqlCommand, indexes, values);
    }

    public int updateData(String sqlCommand, int[] indexes, String[] values) {
        return excuteUpdate(sqlCommand, indexes, values);
    }
    public int updateData(String sqlCommand){
        try {
            return state.executeUpdate(sqlCommand);
        } catch (SQLException ex) {
            Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }
    public int deleteData(String sqlCommand, int[] indexes, String[] values) {
        return excuteUpdate(sqlCommand, indexes, values);
    }

    public int excuteUpdate(String sqlCommand, int[] indexes, String[] values) {
        int rowNo = 0;
        if (sqlCommand == null) {
            throw new NullPointerException("sql command is null");
        }
        PreparedStatement ps;
        try {
            ps = connection.prepareStatement(sqlCommand);
            if (indexes != null && values != null) {
                if (indexes.length != values.length) {
                    throw new NullPointerException();
                }
                for (int i = 0; i < values.length; i++) {
                    ps.setString(indexes[i], values[i]);
                }
            }
            rowNo = ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, ex);
        }
        return rowNo;
    }
}
